Exploratory Data Analysis: Agricultural Commodity Loss Variations across the Pacific Northwest, narrowing to the IPNW
Erich Seamon
03/01/2019
Introduction
This analysis explores the relationships of agricultural commodity loss, at a county level, from 2001-2015, across the three state region of the Pacific Northwest (Washington, Idaho, and Oregon), and then focusing in on the Inland Pacific Northwest (INPW - 26 county region that encapsulates the Palouse, in Washington, Idaho, and Oregon). Here we explore the entire range of commodities and damage causes, identifying the top revenue loss commodities and their most pertinent damage causes - as indicated from the USDA’s agricultural commodity loss insurance archive. We also explore claim freqency, and finally zero in on the top 5 commodities for the region, and examine specific loss and claim frequency by year and damage cause.
We perform several steps to examine the full set of data, and to narrow down factors to a usable form for further modeling analysis.
Step 1. Data Preparation: Loading and Transforming data. Here we load commodity loss data, and aggregate by differing factors, so we can examine losses on an annual basis, by county, damage cause, and commodity.
Step 2. Study Area Overview. We narrow down our data to the 26 county palouse region, and display a study area map for perspective.
Step 3. Pacific Northwest Region Overview of Insurance Loss by year, damage cause, and commodity: 2001-2015
Step 4. Pacific Northwest Region Overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015
Step 5. IPNW Study Area Region Overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015
Step 6. IPNW Study Area Region Interaction Plots of Insurance Loss FREQUENCY, for WHEAT and APPLES: 2001-2015
Step 7. Nationwide Wheat Prices per metric ton: 2001-2015
Step 8. IPNW Study Area Region Overview of Insurance Loss ($) by Year: 1989-2015 We summarize all insurance loss by year for 1989 - 2015 - so we can then subset our analysis to a refined time period (2001-2015).
Step 9. IPNW Study Area Region Overview of Insurance Loss by Commodity: 1989-2015 We summarize all commodity insurance loss for the Palouse by commodity - so we can determine the top insurance loss commodities for a filtered examination.
Step 10. IPNW Study Area Region Overiew of Insurance Loss by Damage Cause: 1989-2015. Similarly to Step 3, we summarize all insurance loss by the cause of the damage for 1989 - 2015, so we can determine which damage causes are resulting the the majority of insurance loss.
Step 11. IPNW Study Area Region Overview of Insurance Loss by Commodity: 2001 - 2015. Given our previous steps, we now examine Palouse commodity insurance loss for 2001 - 2015.
Step 12. IPNW Study Area Region Overiew of Insurance Loss by Damage Cause: 2001-2015. In step 8, we examine Palouse commodities by damage cause for 2001-2015.
Step 13. Top five commodities, 2001-2015 for the IPNW Region Study Area. In step 13, we examine the IPNW top five commodities for 2001-2015 (apples, wheat, cherries, dry peas, and barley).
Step 1. Data Preparation: Loading and Transformation data
The first step is to load Pacific Northwest agricultural insurance commodity loss data, aggregate by county, damage cause and commodity, remove zeros, transform the loss values ($) into cube root and logrithmic outputs, and scale and center the data.
Step 2: Study Area Overview - 26 County Palouse Region
Step 3: Pacific Northwest Region Overview of Insurance Loss by year, damage cause, and commodity: 2001-2015
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damage cause | loss |
| Drought | $719,610,396 |
| Decline in Price | $336,993,981 |
| Heat | $267,376,106 |
| Frost | $170,482,583 |
| Freeze | $149,373,220 |
| Excess Moisture/Precip/Rain | $148,556,853 |
| Hail | $127,772,100 |
| Cold Wet Weather | $86,741,055 |
| Cold Winter | $67,118,523 |
| Failure Irrig Supply | $57,087,916 |
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| WHEAT | $1,444,149,995 |
| CHERRIES | $156,670,513 |
| APPLES | $156,063,380 |
| POTATOES | $117,459,400 |
| BARLEY | $102,125,046 |
| DRY PEAS | $58,038,162 |
| PNW total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| year | loss |
| 2001 | $66,499,267 |
| 2002 | $96,367,644 |
| 2003 | $103,553,264 |
| 2004 | $52,006,242 |
| 2005 | $72,871,250 |
| 2006 | $63,467,130 |
| 2007 | $83,252,301 |
| 2008 | $150,480,619 |
| 2009 | $545,650,029 |
| 2010 | $83,829,994 |
| 2011 | $115,965,036 |
| 2012 | $89,684,692 |
| 2013 | $171,121,810 |
| 2014 | $222,526,098 |
| 2015 | $296,416,750 |
Step 4: Pacific Northwest Region Overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015
Step 5: IPNW Study Area Region Overview of Insurance Loss FREQUENCY by commodity and damage cause: 2001-2015
/
Step 6: IPNW Study Area Region Interaction Plots of Insurance Loss FREQUENCY, for WHEAT and APPLES: 2001-2015
Step 7: Nationwide Wheat Prices per metric ton: 2001-2015
Step 8: IPNW Study Area Region Overview of Insurance Loss ($) by Year: 1989-2015
| IPNW region total insurance loss by year, 2001-2015 | |
| 2001-2015 | |
|---|---|
| year | loss |
| 2001 | $51,669,997 |
| 2002 | $75,193,597 |
| 2003 | $71,122,910 |
| 2004 | $27,690,033 |
| 2005 | $42,829,865 |
| 2006 | $50,433,271 |
| 2007 | $47,600,330 |
| 2008 | $118,284,490 |
| 2009 | $441,709,248 |
| 2010 | $52,081,926 |
| 2011 | $68,218,646 |
| 2012 | $47,232,701 |
| 2013 | $118,373,608 |
| 2014 | $177,267,234 |
| 2015 | $240,900,597 |
Step 9: IPNW Study Area Region Overview of Insurance Loss by Commodity: 2001-2015
| IPNW region total insurance loss by commodity, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| WHEAT | $1,214,345,016 |
| APPLES | $89,191,032 |
| CHERRIES | $74,853,794 |
| DRY PEAS | $56,809,377 |
| BARLEY | $34,102,839 |
| GRAPES | $17,370,434 |
| GREEN PEAS | $13,520,586 |
| POTATOES | $10,232,481 |
| All Other Crops | $9,767,559 |
| CANOLA | $8,913,093 |
| ADJUSTED GROSS REVENUE-LITE | $8,128,725 |
STEP 10: IPNW Study Area Region Overiew of Insurance Loss by Damage Cause: 2001-2015
| IPNW region total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damage cause | loss |
| Drought | $677,383,459 |
| Decline in Price | $290,497,980 |
| Heat | $197,657,025 |
| Frost | $92,483,544 |
| Hail | $81,461,121 |
| Freeze | $77,583,017 |
| Excessive Moisture | $76,603,640 |
| Cold Wet Weather | $54,410,357 |
| Cold Winter | $33,893,991 |
| Wind/Excess Wind | $12,442,392 |
Step 11: IPNW Study Area Region Overview of Insurance Loss by Commodity: 2001 - 2015
| IPNW region total insurance loss by commodity, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| WHEAT | $1,214,345,016 |
| APPLES | $89,191,032 |
| CHERRIES | $74,853,794 |
| DRY PEAS | $56,809,377 |
| BARLEY | $34,102,839 |
| GRAPES | $17,370,434 |
| GREEN PEAS | $13,520,586 |
| POTATOES | $10,232,481 |
| All Other Crops | $9,767,559 |
| CANOLA | $8,913,093 |
| ADJUSTED GROSS REVENUE-LITE | $8,128,725 |
STEP 12: IPNW Study Area Region Overiew of Insurance Loss by Damage Cause: 2001-2015
| IPNW region total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| commodity | loss |
| Drought | $677,383,459 |
| Decline in Price | $290,497,980 |
| Heat | $197,657,025 |
| Frost | $92,483,544 |
| Hail | $81,461,121 |
| Freeze | $77,583,017 |
| Excessive Moisture | $76,603,640 |
| Cold Wet Weather | $54,410,357 |
| Cold Winter | $33,893,991 |
| Wind/Excess Wind | $12,442,392 |
STEP 13: Top five commodities, 2001-2015 for the IPNW Region Study Area
Top Damage Causes for APPLES, 2001-2015 for the Palouse Region
| IPNW region APPLES total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Hail | $40,971,994 |
| Frost | $17,016,586 |
| Freeze | $14,538,852 |
| Cold Wet Weather | $9,246,848 |
| Wind/Excess Wind | $2,934,044 |
| Heat | $2,422,480 |
| Other | $2,060,230 |
Top Damage Causes for BARLEY, 2001-2015 for the Palouse Region
| IPNW region BARLEY total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $19,635,980 |
| Excess Moisture/Precip/Rain | $4,726,399 |
| Heat | $4,553,252 |
| Hail | $1,466,219 |
| Frost | $1,247,180 |
| Other | $1,085,647 |
| Decline in Price | $858,459 |
| Cold Wet Weather | $529,703 |
/
Top Damage Causes for WHEAT, 2001-2015 for the Palouse Region
| IPNW region WHEAT total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $626,038,990 |
| Decline in Price | $243,681,710 |
| Heat | $150,761,018 |
| Frost | $45,605,476 |
| Excess Moisture/Precip/Rain | $33,421,663 |
| Cold Winter | $28,004,014 |
| Freeze | $27,203,439 |
| Cold Wet Weather | $24,511,419 |
| Hail | $20,390,532 |
| Other | $9,724,032 |
| Plant Disease | $2,950,763 |
| Other (Snow-Lightning-Etc.) | $2,051,960 |
## $interactive
## [1] TRUE
##
## $draggable
## [1] FALSE
##
## $keyboard
## [1] TRUE
##
## $title
## [1] ""
##
## $alt
## [1] ""
##
## $zIndexOffset
## [1] 0
##
## $opacity
## [1] 1
##
## $riseOnHover
## [1] TRUE
##
## $riseOffset
## [1] 250
/
Top Damage Causes for CHERRIES, 2001-2015 for the Palouse Region
| IPNW region CHERRIES total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Excessive Moisture | $20,569,890 |
| Frost | $15,459,348 |
| Freeze | $14,625,022 |
| Decline in Price | $8,447,020 |
| Cold Wet Weather | $6,725,206 |
| Heat | $2,246,093 |
| Hail | $2,034,613 |
| Other (Snow-Lightning-Etc.) | $1,876,035 |
| Other | $1,467,713 |
| Wind/Excess Wind | $1,402,854 |
/
Top Damage Causes for DRY PEAS, 2001-2015 for the Palouse Region
| IPNW region DRY PEAS total insurance loss by damage cause, 2001-2015 | |
| 2001-2015 | |
|---|---|
| damagecause | loss |
| Drought | $22,635,712 |
| Heat | $10,399,165 |
| Excessive Moisture | $8,618,433 |
| Hail | $5,176,545 |
| Cold Wet Weather | $2,971,140 |
| Decline in Price | $2,936,766 |
| Other | $2,067,635 |
| Wind/Excess Wind | $1,079,927 |
| Frost | $924,053 |
/